package com.leezw.springbootinit.utils;
import com.leezw.springbootinit.common.ErrorCode;
import com.leezw.springbootinit.mapper.FileMapper;
import com.leezw.springbootinit.model.entity.Financial;
import com.leezw.springbootinit.model.entity.Flow;
import com.leezw.springbootinit.model.entity.Member;
import lombok.extern.slf4j.Slf4j;
import com.leezw.springbootinit.exception.BusinessException;
import lombok.SneakyThrows;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Component;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletRequest;

import java.io.*;
import java.math.BigDecimal;
import java.net.InetAddress;
import java.sql.Time;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * @author lizhouwei
 */
@Component
@Slf4j
public class FileUtils {
    /**
     * 图片保存路径，自动从yml配置文件中获取数据
     */
    @Value("${file.path}")
    private String uploadPath;

    @Autowired
    private FileMapper fileMapper;



    /**
     * 文件（图片）上传
     *
     * @param file 图片文件
     */
    @SneakyThrows
    public String uploadFile(MultipartFile file, HttpServletRequest request) {

        //文件原名称
        String oldFilename = file.getOriginalFilename();
        //图片名后缀：.jpg、.png
        String suffix = oldFilename.substring(oldFilename.lastIndexOf("."));
        //uuid
        String uuid = UUID.randomUUID().toString();
        //文件新名称
        String newFileName = uuid + suffix;
        try {
            //创建保存上传文件的文件夹
            File folder = new File(uploadPath + newFileName);
            if (!folder.getParentFile().exists()) {
                folder.getParentFile().mkdirs();
            }
            //文件写入到该文件夹下
            file.transferTo(folder);
            // 返回可访问地址
        } catch (Exception e) {
            log.error("file upload error, filepath = " + newFileName, e);
            throw new BusinessException(ErrorCode.SYSTEM_ERROR, "上传失败");
        }
        //获得本机Ip（获取的是服务器的Ip）
        InetAddress inetAddress = InetAddress.getLocalHost();
        String ip = inetAddress.getHostAddress();
        //返回保存的url，根据url可以进行文件查看或者下载
        String fileDownloadUrl = request.getScheme() + "://" + ip + ":" + request.getServerPort() + "/api/file/" + newFileName;

        //返回保存的url
        return fileDownloadUrl;

    }

    /**
     * @author 她比代码难懂
     * @date 2024/5/19
     * @description 导入流水文件
     */
    @Transactional
    public int FlowFile(MultipartFile file) throws Exception {
        InputStream is = file.getInputStream();
        try {
            SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd");
            SimpleDateFormat simpleDateFormat2 = new SimpleDateFormat("HH:mm:ss");
            XSSFWorkbook excels = new XSSFWorkbook(is);
            XSSFSheet sheet = excels.getSheetAt(1);
            int lastRowNum = sheet.getLastRowNum();
            Flow sheel = null;
            List<String> list = null;
            XSSFRow headerRow = sheet.getRow(0);
            int cellCount = headerRow.getPhysicalNumberOfCells();
            int successCount = 0;
            for (int i = 1; i <= lastRowNum; i++) {

                list = new ArrayList<>();
                XSSFRow row = sheet.getRow(i);
                for (int cn = 0; cn < cellCount; cn++) {
                    XSSFCell c = row.getCell(cn, Row.MissingCellPolicy.RETURN_BLANK_AS_NULL);
                    if (c == null) {
                        // The spreadsheet is empty in this cell
                        list.add(null);
                    } else {
                        switch (c.getCellType()) {
                            case STRING:
                                list.add(c.getStringCellValue());
                                break;
                            case NUMERIC:
                                if (DateUtil.isCellDateFormatted(c)) {
                                    // 如果是日期类型
                                    Date date = c.getDateCellValue();
                                    if (DateUtils.isTime(date)) {
                                        // 如果只包含时间，则按时间格式化
                                        list.add(simpleDateFormat2.format(date));
                                    } else {
                                        // 否则按日期格式化
                                        list.add(simpleDateFormat.format(date));
                                    }
                                } else {
                                    // 否则，认为是数字
                                    DecimalFormat decimalFormat = new DecimalFormat("#.##########");
                                    String cellValue = decimalFormat.format(c.getNumericCellValue());
                                    list.add(cellValue);
                                }
                                break;
                            default:
                                list.add(null);
                        }
                    }
                }
                sheel = new Flow();
                for (int j = 0; j < list.size(); j++) {
                    String value = list.get(j);
                    if (value != null) {
                        switch (j) {
                            case 0 -> sheel.setSalesPersonNumber(value);
                            case 1 -> sheel.setSerialNumber(value);
                            case 2 -> sheel.setCategory(value);
                            case 3 -> sheel.setDate(simpleDateFormat.parse(value));
                            case 4 -> sheel.setCashierNumber(value);
                            case 5 -> sheel.setStaffName(value);
                            case 6 -> sheel.setProductName(value);
                            case 7 -> sheel.setProductNumber(value);
                            case 8 -> sheel.setProductSpecification(value);
                            case 9 -> sheel.setUnit(value);
                            case 10 ->sheel.setOrigin(value);
                            case 11 ->sheel.setMachineDate(simpleDateFormat.parse(value));
                            case 12 ->sheel.setTime(Time.valueOf(value));
                            case 13 ->sheel.setQuantity(Integer.valueOf(value));
                            case 14 ->sheel.setRetailPrice(BigDecimal.valueOf(Double.parseDouble(value)));
                            case 15 ->sheel.setBatchNumber(value);
                            case 16 ->sheel.setExpiryDate(simpleDateFormat.parse(value));
                            case 17 ->sheel.setCostPrice(BigDecimal.valueOf(Double.parseDouble(value)));
                            case 18 ->sheel.setReceivableAmount(BigDecimal.valueOf(Double.parseDouble(value)));
                            case 19 ->sheel.setNetRevenue(BigDecimal.valueOf(Double.parseDouble(value)));
                            case 20 ->sheel.setStockQuantity(Double.valueOf(value).intValue());
                            case 21 ->sheel.setCustomerCode(value);
                            default -> {
                            }
                            // 如果有其他字段，可以在这里处理
                        }
                    } else {
                        // 如果值为null，则设置相应的字段为null
                        switch (j) {
                            case 0 -> sheel.setSalesPersonNumber(null);
                            case 1 -> sheel.setSerialNumber(null);
                            case 2 -> sheel.setCategory(null);
                            case 3 -> sheel.setDate(null);
                            case 4 -> sheel.setCashierNumber(null);
                            case 5 -> sheel.setStaffName(null);
                            case 6 -> sheel.setProductName(null);
                            case 7 -> sheel.setProductNumber(null);
                            case 8 -> sheel.setProductSpecification(null);
                            case 9 -> sheel.setUnit(null);
                            case 10 ->sheel.setOrigin(null);
                            case 11 ->sheel.setMachineDate(null);
                            case 12 ->sheel.setTime(null);
                            case 13 ->sheel.setQuantity(null);
                            case 14 ->sheel.setRetailPrice(null);
                            case 15 ->sheel.setBatchNumber(null);
                            case 16 ->sheel.setExpiryDate(null);
                            case 17 ->sheel.setCostPrice(null);
                            case 18 ->sheel.setReceivableAmount(null);
                            case 19 ->sheel.setNetRevenue(null);
                            case 20 ->sheel.setStockQuantity(null);
                            case 21 ->sheel.setCustomerCode(null);
                            default -> {
                            }
                            // 如果有其他字段，可以在这里处理
                        }
                    }
                }
                int insertResult = fileMapper.insertFlow(sheel);
                if (insertResult > 0) {
                    successCount++; // 如果插入成功，则增加成功计数器
                }
            }
            System.out.println("成功导入" + successCount + "条数据");
            return successCount;
        } finally {
            is.close();
        }
    }

    /**
     * @author 她比代码难懂
     * @date 2024/5/19
     * @description 导入会员文件
     */
    @Transactional
    public int MemberFile(MultipartFile file) throws Exception {
        InputStream is = file.getInputStream();
        try {
            SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd");
            SimpleDateFormat simpleDateFormat2 = new SimpleDateFormat("HH:mm:ss");
            XSSFWorkbook excels = new XSSFWorkbook(is);
            XSSFSheet sheet = excels.getSheetAt(2);
            int lastRowNum = sheet.getLastRowNum();
            Member memberTable = null;
            List<String> list = null;
            XSSFRow headerRow = sheet.getRow(0);
            int cellCount = headerRow.getPhysicalNumberOfCells();
            int successCount = 0;
            for (int i = 1; i <= lastRowNum; i++) {

                list = new ArrayList<>();
                XSSFRow row = sheet.getRow(i);
                for (int cn = 0; cn < cellCount; cn++) {
                    XSSFCell c = row.getCell(cn, Row.MissingCellPolicy.RETURN_BLANK_AS_NULL);
                    if (c == null) {
                        // The spreadsheet is empty in this cell
                        list.add(null);
                    } else {
                        switch (c.getCellType()) {
                            case STRING:
                                list.add(c.getStringCellValue());
                                break;
                            case NUMERIC:
                                if (DateUtil.isCellDateFormatted(c)) {
                                    // 如果是日期类型
                                    Date date = c.getDateCellValue();
                                    if (DateUtils.isTime(date)) {
                                        // 如果只包含时间，则按时间格式化
                                        list.add(simpleDateFormat2.format(date));
                                    } else {
                                        // 否则按日期格式化
                                        list.add(simpleDateFormat.format(date));
                                    }
                                } else {
                                    // 否则，认为是数字
                                    DecimalFormat decimalFormat = new DecimalFormat("0.#########");
                                    String cellValue = decimalFormat.format(c.getNumericCellValue());
                                    list.add(cellValue);
                                }
                                break;
                            default:
                                list.add(null);
                        }
                    }
                }
                memberTable = new Member();
                for (int j = 0; j < list.size(); j++) {
                    String value = list.get(j);
                    if (value != null) {
                        switch (j) {
                            case 0 -> memberTable.setCardNumber(value);
                            case 1 -> memberTable.setCardHolderName(value);
                            case 2 -> memberTable.setMnemonicCode(value);
                            case 3 -> memberTable.setIdNumber(value);
                            case 4 -> memberTable.setGender(value);
                            case 5 -> memberTable.setBirthday(simpleDateFormat.parse(value));
                            case 6 -> memberTable.setNoUse(value);
                            case 7 -> memberTable.setMobilePhone(value);
                            case 8 -> memberTable.setAddress(value);
                            case 9 -> memberTable.setPhone(value);
                            case 10 -> memberTable.setRemarks(value);
                            case 11 -> memberTable.setIsJf(Integer.valueOf(value));
                            case 12 -> memberTable.setMemberCardDiscount(BigDecimal.valueOf(Double.valueOf(value).intValue()));
                            case 13 -> memberTable.setReturnVisitTimes(Integer.valueOf(value));
                            case 14 -> memberTable.setIsInsert(Integer.valueOf(value));
                            case 15 -> memberTable.setIsUpdate(Integer.valueOf((value)));
                            case 16 -> memberTable.setCardCreationStore(value);
                            default -> {
                            }
                            // 如果有其他字段，可以在这里处理
                        }
                    } else {
                        // 如果值为null，则设置相应的字段为null
                        switch (j) {
                            case 0 -> memberTable.setCardNumber(null);
                            case 1 -> memberTable.setCardHolderName(null);
                            case 2 -> memberTable.setMnemonicCode(null);
                            case 3 -> memberTable.setIdNumber(null);
                            case 4 -> memberTable.setGender(null);
                            case 5 -> memberTable.setBirthday(null);
                            case 6 -> memberTable.setNoUse(null);
                            case 7 -> memberTable.setMobilePhone(null);
                            case 8 -> memberTable.setAddress(null);
                            case 9 -> memberTable.setPhone(null);
                            case 10 -> memberTable.setRemarks(null);
                            case 11 -> memberTable.setIsJf(null);
                            case 12 -> memberTable.setMemberCardDiscount(null);
                            case 13 -> memberTable.setReturnVisitTimes(null);
                            case 14 -> memberTable.setIsInsert(null);
                            case 15 -> memberTable.setIsUpdate(null);
                            case 16 -> memberTable.setCardCreationStore(null);
                            default -> {
                            }
                            // 如果有其他字段，可以在这里处理
                        }
                    }
                }
                int insertResult = fileMapper.insertMember(memberTable);
                if (insertResult > 0) {
                    successCount++; // 如果插入成功，则增加成功计数器
                }
            }
            System.out.println("成功导入" + successCount + "条数据");
            return successCount;
        } finally {
            is.close();
        }
    }

    /**
     * @author 她比代码难懂
     * @date 2024/5/21
     * @description 导入财务文件
     */
    @Transactional
    public int FinancialFile(MultipartFile excelFile) throws Exception {
        InputStream is = excelFile.getInputStream();
        try {
            SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd");
            SimpleDateFormat simpleDateFormat2 = new SimpleDateFormat("HH:mm:ss");
            XSSFWorkbook excels = new XSSFWorkbook(is);
            XSSFSheet sheet = excels.getSheetAt(0);
            int lastRowNum = sheet.getLastRowNum();
            Financial financialFile =null;
            List<String> list = null;
            XSSFRow headerRow = sheet.getRow(0);
            int cellCount = headerRow.getPhysicalNumberOfCells();
            int successCount = 0;
            for (int i = 1; i <= lastRowNum; i++) {

                list = new ArrayList<>();
                XSSFRow row = sheet.getRow(i);
                for (int cn = 0; cn < cellCount; cn++) {
                    XSSFCell c = row.getCell(cn, Row.MissingCellPolicy.RETURN_BLANK_AS_NULL);
                    if (c == null) {
                        // The spreadsheet is empty in this cell
                        list.add(null);
                    } else {
                        switch (c.getCellType()) {
                            case STRING:
                                list.add(c.getStringCellValue());
                                break;
                            case NUMERIC:
                                if (DateUtil.isCellDateFormatted(c)) {
                                    // 如果是日期类型
                                    Date date = c.getDateCellValue();
                                    if (DateUtils.isTime(date)) {
                                        // 如果只包含时间，则按时间格式化
                                        list.add(simpleDateFormat2.format(date));
                                    } else {
                                        // 否则按日期格式化
                                        list.add(simpleDateFormat.format(date));
                                    }
                                } else {
                                    // 否则，认为是数字
                                    DecimalFormat decimalFormat = new DecimalFormat("0.#########");
                                    String cellValue = decimalFormat.format(c.getNumericCellValue());
                                    list.add(cellValue);
                                }
                                break;
                            default:
                                list.add(null);
                        }
                    }
                }
                financialFile = new Financial();
                for (int j = 0; j < list.size(); j++) {
                    String value = list.get(j);
                    if (value != null) {
                        switch (j) {
                            case 0 -> financialFile.setDate(simpleDateFormat.parse(value));
                            case 1 -> financialFile.setNetRevenue(BigDecimal.valueOf(Double.parseDouble(value)));
                            case 2 -> financialFile.setCostAmount(BigDecimal.valueOf(Double.parseDouble(value)));
                            case 3 -> financialFile.setGrossProfit(BigDecimal.valueOf(Double.parseDouble(value)));
                            case 4 -> financialFile.setGrossMargin(BigDecimal.valueOf(Double.parseDouble(value)));
                            case 5 -> financialFile.setAveragePrice(BigDecimal.valueOf(Double.parseDouble(value)));
                            case 6 -> financialFile.setCustomerCount(Integer.valueOf((value)));
                            case 7 -> financialFile.setProductVariety(Integer.valueOf((value)));
                            case 8 -> financialFile.setMemberCount(Integer.valueOf((value)));
                            case 9  ->financialFile.setMemberNetRevenue(BigDecimal.valueOf(Double.parseDouble(value)));
                            case 10 ->financialFile.setMemberGrossProfit(BigDecimal.valueOf(Double.parseDouble(value)));
                            case 11 ->financialFile.setMemberRevenueRatio(BigDecimal.valueOf(Double.parseDouble(value)));
                            default -> {
                            }
                            // 如果有其他字段，可以在这里处理
                        }
                    } else {
                        // 如果值为null，则设置相应的字段为null
                        switch (j) {
                            case 0 -> financialFile.setDate(null);
                            case 1 -> financialFile.setNetRevenue(null);
                            case 2 -> financialFile.setCostAmount(null);
                            case 3 -> financialFile.setGrossProfit(null);
                            case 4 -> financialFile.setGrossMargin(null);
                            case 5 -> financialFile.setAveragePrice(null);
                            case 6 -> financialFile.setCustomerCount(null);
                            case 7 -> financialFile.setProductVariety(null);
                            case 8 -> financialFile.setMemberCount(null);
                            case 9  ->financialFile.setMemberNetRevenue(null);
                            case 10 ->financialFile.setMemberGrossProfit(null);
                            case 11 ->financialFile.setMemberRevenueRatio(null);
                            default -> {
                            }
                            // 如果有其他字段，可以在这里处理
                        }
                    }
                }
                int insertResult = fileMapper.insertFinancial(financialFile);
                if (insertResult > 0) {
                    successCount++; // 如果插入成功，则增加成功计数器
                }
            }
            System.out.println("成功导入" + successCount + "条数据");
            return successCount;
        } finally {
            is.close();
        }
    }
}








